-- @owner: zou_jialiang050
-- @date: 2023/4/12
-- @testpoint: 语句添加definer参数,用户有function权限;调用成功
--modified by: zhanghuan，2025.1.2，补充连接B库信息
@conn PrimaryDbBmode;
--step1:创建用户;expect:成功
create user if not exists u_alter_005a password 'test@1234';
create user if not exists u_alter_005b sysadmin password 'test@1234';
create user if not exists u_alter_005c sysadmin password 'test@1234';
create schema sch_alter_005;
grant all on schema sch_alter_005 to u_alter_005a, u_alter_005b, u_alter_005c;

--step2:使用普通用户创建function;expect:成功
set role u_alter_005a password 'test@1234';
drop table if exists sch_alter_005.t_alter_005a;
create table sch_alter_005.t_alter_005a(c1 int);
insert into sch_alter_005.t_alter_005a values(100);
create or replace function sch_alter_005.f_alter_005a()
returns int
as $$
declare
total int;
begin
select * into total from t_alter_005a;
return total;
end;
$$ language plpgsql;
/
alter function sch_alter_005.f_alter_005a sql security definer;

--step3:使用系统管理员用户调用function;expect:成功
alter session set session authorization default;
alter session set session authorization u_alter_005b password 'test@1234';
call sch_alter_005.f_alter_005a();

--step4:使用系统管理员用户创建function;expect:成功
alter session set session authorization default;
alter session set session authorization u_alter_005c password 'test@1234';
drop table if exists sch_alter_005.t_alter_005c;
create table sch_alter_005.t_alter_005c(c1 int);
insert into sch_alter_005.t_alter_005c values(100);
create or replace function sch_alter_005.f_alter_005c()
returns int
as $$
declare
total int;
begin
select * into total from t_alter_005c;
return total;
end;
$$ language plpgsql;
/
alter function sch_alter_005.f_alter_005c sql security definer;

--step5:使用系统管理员用户调用function;expect:成功
alter session set session authorization default;
alter session set session authorization u_alter_005b password 'test@1234';
call sch_alter_005.f_alter_005c();

--step6:清理环境;expect:成功
alter session set session authorization default;
drop table if exists sch_alter_005.t_alter_005a;
drop table if exists sch_alter_005.t_alter_005c;
drop function if exists sch_alter_005.f_alter_005a;
drop function if exists sch_alter_005.f_alter_005c;
drop user if exists u_alter_005a cascade;
drop user if exists u_alter_005b cascade;
drop user if exists u_alter_005c cascade;
drop schema if exists sch_alter_005 cascade;